package com.loong.android.sqlite;

import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.loong.android.sqlite.data.DbData;
import com.loong.android.sqlite.data.EntityFieldData;
import com.loong.android.sqlite.data.TableData;
import com.loong.android.sqlite.utils.DataBaseUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Author: xili
 * Date: 2019/9/26
 * Description: 数据库迁移
 */
public class MigrationHelper {
    private static final String TAG = "MigrationHelper";
    private static final String SQLITE_MASTER = "sqlite_master";
    private static final String SQLITE_TEMP_MASTER = "sqlite_temp_master";

    /**
     * 迁移数据库
     * @param database 数据库
     * @param dbData 数据库信息
     */
    public static void migrate(SQLiteDatabase database, DbData dbData) {
        printLog("【Generate temp table】start");
        generateTempTables(database, dbData);
        printLog("【Generate temp table】complete");

        dropDbAllTable(database, dbData, true);
        createDbAllTable(database, dbData, true);

        printLog("【Restore data】start");
        restoreData(database, dbData);
        printLog("【Restore data】complete");
    }

    private static void dropDbAllTable(SQLiteDatabase sqLiteDatabase, DbData dbData, boolean ifExists) {
        if (dbData == null || dbData.getTableList() == null || sqLiteDatabase == null) return;
        for (TableData tableData : dbData.getTableList()) {
            if (tableData == null) continue;
            dropTable(sqLiteDatabase, tableData, ifExists);
        }
    }

    private static void dropTable(SQLiteDatabase sqLiteDatabase, TableData tableData, boolean ifExists) {
        String sql = "DROP TABLE " + (ifExists ? "IF EXISTS " : "") + "\"" + tableData.getTableName() + "\"";
        sqLiteDatabase.execSQL(sql);
    }

    private static void createAllTable(SQLiteDatabase sqLiteDatabase, boolean ifNotExists) {
        for (Map.Entry<String, DbData> entry : DataBaseUtils.mDbList.entrySet()) {
            createDbAllTable(sqLiteDatabase, entry.getValue(), ifNotExists);
        }
    }

    public static void createDbAllTable(SQLiteDatabase sqLiteDatabase, DbData dbData, boolean ifNotExists) {
        if (dbData == null || dbData.getTableList() == null || sqLiteDatabase == null) return;
        for (TableData tableData : dbData.getTableList()) {
            if (tableData == null) continue;
            createTable(sqLiteDatabase, tableData, ifNotExists);
        }
    }

    private static void createDbAllTable(SQLiteDatabase sqLiteDatabase, String dbName, boolean ifNotExists) {
        if (TextUtils.isEmpty(dbName)) return;
        createDbAllTable(sqLiteDatabase, DataBaseUtils.getDbData(dbName), ifNotExists);
    }

    private static void createTable(SQLiteDatabase sqLiteDatabase, TableData tableData, boolean ifNotExists) {

        String constraint = ifNotExists ? "IF NOT EXISTS " : "";
        StringBuilder sql = new StringBuilder("CREATE TABLE " + constraint + "\"" + tableData.getTableName() + "\" (");
        boolean isFirst = true;
        for (EntityFieldData data : tableData.getColumnKeyList()) {
            if (data == null) continue;
            boolean isKey = data.getName().equals(CommonEntity.ID_KEY);
            sql.append(isFirst ? "" : ", ");
            sql.append("\"");
            sql.append(data.getColumnName());
            sql.append("\" ");
            sql.append(CommonEntityDao.getSqliteColumnTypeByStr(data.getType()));

            /*if (data.getDefVal() != null) {
                sql.append(" ");
                sql.append("DEFAULT '");
                sql.append(data.getDefVal());
                sql.append("'");
            }*/

            sql.append(isKey ? " PRIMARY KEY AUTOINCREMENT" : "");

            isFirst = false;
        }

        sql.append(");");

        Log.e(TAG, "createTable:  " + sql.toString());

        sqLiteDatabase.execSQL(sql.toString());
    }

    private static void generateTempTables(SQLiteDatabase db, DbData dbData) {
        if (db == null || dbData == null || dbData.getTableList() == null || dbData.getTableList().size() <= 0) return;

        for (TableData tableData : dbData.getTableList()) {
            if (tableData == null || TextUtils.isEmpty(tableData.getTableName())) continue;

            String tempTableName = null;

            String tableName = tableData.getTableName();
            if (!isTableExists(db, false, tableName)) {
                printLog("【New Table】" + tableName);
                continue;
            }
            try {
                tempTableName = tableName.concat("_TEMP");
                StringBuilder dropTableStringBuilder = new StringBuilder();
                dropTableStringBuilder.append("DROP TABLE IF EXISTS ").append(tempTableName).append(";");
                db.execSQL(dropTableStringBuilder.toString());

                StringBuilder insertTableStringBuilder = new StringBuilder();
                insertTableStringBuilder.append("CREATE TEMPORARY TABLE ").append(tempTableName);
                insertTableStringBuilder.append(" AS SELECT * FROM ").append(tableName).append(";");
                db.execSQL(insertTableStringBuilder.toString());
                printLog("【Generate temp table】" + tempTableName);
            } catch (SQLException e) {
                Log.e(TAG, "【Failed to generate temp table】" + tempTableName, e);
            }
        }
    }

    /**
     * 判断某个表是否存在
     * @param db 数据库
     * @param isTemp 是否为临时表
     * @param tableName 表名称
     * @return 返回真为存在，假为不存在
     */
    private static boolean isTableExists(SQLiteDatabase db, boolean isTemp, String tableName) {
        if (db == null || TextUtils.isEmpty(tableName)) {
            return false;
        }
        String dbName = isTemp ? SQLITE_TEMP_MASTER : SQLITE_MASTER;
        String sql = "SELECT COUNT(*) FROM " + dbName + " WHERE type = ? AND name = ?";
        Cursor cursor = null;
        int count = 0;
        try {
            cursor = db.rawQuery(sql, new String[]{"table", tableName});
            if (cursor == null || !cursor.moveToFirst()) {
                return false;
            }
            count = cursor.getInt(0);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) cursor.close();
        }
        return count > 0;
    }

    private static void restoreData(SQLiteDatabase db, DbData dbData) {

        for (TableData tableData : dbData.getTableList()) {
            if (tableData == null || TextUtils.isEmpty(tableData.getTableName())) continue;

            String tableName = tableData.getTableName();

            String tempTableName = tableName.concat("_TEMP");

            if (!isTableExists(db, true, tempTableName)) {
                continue;
            }

            try {
                // get all columns from tempTable, take careful to use the columns list
                List<TableInfo> newTableInfos = TableInfo.getTableInfo(db, tableName);
                List<TableInfo> tempTableInfos = TableInfo.getTableInfo(db, tempTableName);
                ArrayList<String> selectColumns = new ArrayList<>(newTableInfos.size());
                ArrayList<String> intoColumns = new ArrayList<>(newTableInfos.size());
                for (TableInfo tableInfo : tempTableInfos) {
                    if (newTableInfos.contains(tableInfo)) {
                        String column = '`' + tableInfo.name + '`';
                        intoColumns.add(column);
                        selectColumns.add(column);
                    }
                }
                // NOT NULL columns list
                for (TableInfo tableInfo : newTableInfos) {
                    if (tableInfo.notnull && !tempTableInfos.contains(tableInfo)) {
                        String column = '`' + tableInfo.name + '`';
                        intoColumns.add(column);

                        String value;
                        if (tableInfo.dfltValue != null) {
                            value = "'" + tableInfo.dfltValue + "' AS ";
                        } else {
                            value = "'' AS ";
                        }
                        selectColumns.add(value + column);
                    }
                }

                if (intoColumns.size() != 0) {
                    StringBuilder insertTableStringBuilder = new StringBuilder();
                    insertTableStringBuilder.append("REPLACE INTO ").append(tableName).append(" (");
                    insertTableStringBuilder.append(TextUtils.join(",", intoColumns));
                    insertTableStringBuilder.append(") SELECT ");
                    insertTableStringBuilder.append(TextUtils.join(",", selectColumns));
                    insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");
                    db.execSQL(insertTableStringBuilder.toString());
                    printLog("【Restore data】 to " + tableName);
                }
                StringBuilder dropTableStringBuilder = new StringBuilder();
                dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);
                db.execSQL(dropTableStringBuilder.toString());
                printLog("【Drop temp table】" + tempTableName);
            } catch (SQLException e) {
                Log.e(TAG, "【Failed to restore data from temp table 】" + tempTableName, e);
            }

        }
    }

    private static void printLog(String info) {
        Log.d(TAG, info);
    }

    private static class TableInfo {
        int cid;
        String name;
        String type;
        boolean notnull;
        String dfltValue;
        boolean pk;

        @Override
        public boolean equals(Object o) {
            return this == o
                    || o != null
                    && getClass() == o.getClass()
                    && name.equals(((TableInfo) o).name);
        }

        @Override
        public String toString() {
            return "TableInfo{" +
                    "cid=" + cid +
                    ", name='" + name + '\'' +
                    ", type='" + type + '\'' +
                    ", notnull=" + notnull +
                    ", dfltValue='" + dfltValue + '\'' +
                    ", pk=" + pk +
                    '}';
        }

        private static List<TableInfo> getTableInfo(SQLiteDatabase db, String tableName) {
            String sql = "PRAGMA table_info(" + tableName + ")";
            printLog(sql);
            Cursor cursor = db.rawQuery(sql, null);
            if (cursor == null)
                return new ArrayList<>();

            TableInfo tableInfo;
            List<TableInfo> tableInfos = new ArrayList<>();
            while (cursor.moveToNext()) {
                tableInfo = new TableInfo();
                tableInfo.cid = cursor.getInt(0);
                tableInfo.name = cursor.getString(1);
                tableInfo.type = cursor.getString(2);
                tableInfo.notnull = cursor.getInt(3) == 1;
                tableInfo.dfltValue = cursor.getString(4);
                tableInfo.pk = cursor.getInt(5) == 1;
                tableInfos.add(tableInfo);
                // printLog(tableName + "：" + tableInfo);
            }
            cursor.close();
            return tableInfos;
        }
    }


}
